﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

/*
	10/07/2012 Big refactor done.
*/

namespace SqlServerDriver
{
	public class SqlServerDataSource : DbViewDriver.IDataSource,  IDisposable
	{
		//////////////////////////////////////////////////////////
		// Central resource.
		// 
		static DbViewDriver.IDataSource g_source = new SqlServerDataSource();
		public static DbViewDriver.IDataSource Source { get { return g_source; } }

		public SqlServerDataSource()
		{
			// initialise classes to handle various groups of driver functions
			// DataTable generation from SQL
			this.tableCommandProcessor = new TableCommandProcessor(this);
			// Source code generation and searching
			this.sourcecodeCommandProcessor = new SourcecodeCommandProcessor(this);
			// other functions 
			this.extensionCommandProcessor = new ExtensionCommandProcessor(this);
			
			// add specialised help 
			Cmd.ResetHelp(DbView.CommandHelpRepository.Instance);
			
			// Start the thread to check for old connections
			ConnectionMap.Instance.StartCleanupMonitor();
		}
		
		~SqlServerDataSource()
		{
			// On application shutdown you have to hope this is triggered.
			ConnectionMap.Instance.StopCleanupMonitor();
		}

		public void Dispose()
		{
			// Main program should call dispose on each driver it knows about.
			ConnectionMap.Instance.StopCleanupMonitor();
		}

		//////////////////////////////////////////////////////////
		// interface DbViewDriver.IDataSource
		// 
		public String ConnectionString
		{
			get { return connectionString; }
		}
		// unit testing
		public void SetConnectionString(String connectionStringParam) { this.connectionString = connectionStringParam ; }

		public bool IsDevelopmentDatabase
		{
			get { return connectionInfo.AllowChanges; }
		}

		public bool PerformDriverCheck(out String configErrorMessage)
		{
			try 
			{
				using (SqlConnection sqlConn = new SqlConnection())
				{
					configErrorMessage = "OK";
					return true;
				}
			}
			catch(Exception exc)
			{
				// not expecting this ever (ha ha)
				configErrorMessage = String.Format(
@"SQL Server Driver has hit an unexpected error condition. Details are below. 
Please refer to the DbViewSharp website for details on how to fix this. If
there are none then please consider contacting the project administrator 
with details. The message reads:

{0}", exc.Message);
				return false;
			}
		}

		public void SetCurrentConnection(DbView.ConnectionInfo connectionInfo)
		{
			this.connectionInfo = connectionInfo;
			this.connectionString = MakeConnectionString(connectionInfo);
			// reset version to unk, which also retests for case-sensitivity
			this.sqlVersion = SqlServerTemplates.Version.UNKNOWN;
		}
		
		// Who is this driver?
		public String DriverName {	get { return "SQL Server"; } }

		public bool SupportsCommand(DbView.Commands.Cmnd command)
		{
			// [Specialise: add supported commands ]
			if (command == DbView.Commands.Cmnd.Nothing) return true ; // sample to override template test
			
			if (this.sqlVersion == SqlServerTemplates.Version.UNKNOWN) DetermineSqlProperties();
			// default: see whether you get a template back. Return T if yes F otherwise
			String commandTemplate = SqlServerTemplates.Templates.FetchTemplate(SqlServerTemplates.MapVersion(this.sqlVersion), command);
			return (commandTemplate.Length > 0);
		}
		// define string constants to use as special commands
		
		// build the custom main menu 
		public DbViewDriver.CustomMenuItem BuildCustomMenu()
		{
			if (this.sqlVersion == SqlServerTemplates.Version.UNKNOWN) DetermineSqlProperties();
			return new SqlServerPopupBuilder(IsDevelopmentDatabase).BuildCustomMenu();
		}

		// build a pop-up menu 
		public DbViewDriver.CustomMenuItem BuildPopupMenu(DbView.Command command)
		{
			return new SqlServerPopupBuilder(IsDevelopmentDatabase).BuildPopupMenu(command);
		}

		//////////////////// CommandBuilder Interface ////////
		public String MakeCommand(DbView.Command command, DbView.ViewContext context)
		{
			// get the version as it may change the SQL used
			SqlServerTemplates.Version databaseVersion = sqlVersion;
			if (this.sqlVersion == SqlServerTemplates.Version.UNKNOWN)
				databaseVersion = DetermineSqlProperties();
			// the idea for plugins is to register builders here
			// for now we'll just deal with known 
			String filterString = (this.isDbCaseSensitive) ? context.FilterString.ToUpper() : context.FilterString;
			// look up. The commented out version can fail wierdly
			String commandTemplate = SqlServerTemplates.Templates.FetchTemplate(SqlServerTemplates.MapVersion(databaseVersion), command.Cmd);
			//String commandTemplate = SqlServerTemplates.Templates.FetchTemplate(MapVersion(this.m_SqlVersion), command.Cmd);
			if (command.Cmd == DbView.Commands.Cmnd.Extension)
				commandTemplate = SqlServerTemplates.Templates.FetchExtensionTemplate(SqlServerTemplates.MapVersion(databaseVersion), command, command.Name);
			// if no additional parameter assume just the filter is required
			if (this.tableCommandProcessor.SimpleTemplate(commandTemplate))
			{
				return tableCommandProcessor.AddFilterClause(commandTemplate, filterString);
			}
			// [Specialise: custom sql command building ]
			switch (command.Cmd)
			{
				case DbView.Commands.Cmnd.Adhoc:
					// return filterString; // was this, but fails for case-sensitve databases.
					return context.FilterString;
				case DbView.Commands.Cmnd.Fields:
					return tableCommandProcessor.MakeFieldsCommandString(context, commandTemplate, filterString);
				case DbView.Commands.Cmnd.ViewSource:
					return SourceCommand(context);
				case DbView.Commands.Cmnd.ProcedureSource:
					return SourceCommand(context);
				case DbView.Commands.Cmnd.TableFieldSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.ProcedureSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.AllSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.ViewSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.FunctionSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.TriggerSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.TableData:
				{
					//String tableName = TableNameFromContext(context);
					return tableCommandProcessor.TableDataCommandString(context, filterString);
				}
				case DbView.Commands.Cmnd.Aggregate:
					return tableCommandProcessor.AggregateDataCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.RowCount:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.MinMax:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.Average:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.LongShort:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.Dependencies:
					return tableCommandProcessor.MakeDependenciesCommand(commandTemplate, command, filterString);
				case DbView.Commands.Cmnd.Permissions:
					return tableCommandProcessor.MakePermissionsCommand(commandTemplate, command, filterString);
				case DbView.Commands.Cmnd.Extension:
					switch (command.Name)
					{
						case Cmd.Descriptions:
							return tableCommandProcessor.MakeFieldDescriptionsCommandString(command[DbView.Command.TableParameterName(0)], commandTemplate, filterString);
						case Cmd.JobSteps:
							String jobId = command[CmdParam.JobId];
							return String.Format(commandTemplate, filterString, jobId);
						case Cmd.JobHistory:
							return String.Format(commandTemplate, filterString, command[CmdParam.JobId]);
						case Cmd.Articles:
							return tableCommandProcessor.MakeArticlesCommand(commandTemplate, command, filterString);
						default:
							if (SqlServerBrokerPopupBuilder.IsBrokerCommand(command))
								return SqlServerBrokerPopupBuilder.MakeBrokerCommand(commandTemplate, command, filterString);
							break;
					}
					break;
			}
			return ""; // ie unsupported
		}

		// core Interface method to build a data table
		public DataTable BuildTable(DbView.Command command, DbView.ViewContext context)
		{
			// 
			String commandString = MakeCommand(command, context);
			if (commandString.Length == 0)
				throw new Exception(String.Format("Driver Error: {0} generated an empty command string", command));
			
			return tableCommandProcessor.BuildDataTable(command, commandString, context);
		}

		// updates the a table folowing edits
		public bool Update(DataTable table, out String statusMessage)
		{
			// need to build some sql
			//DbView.ViewDefinition viewDef = null;
			DbView.ViewDefinition viewDef = new DbView.ViewDefinition();
			viewDef.ConfigureFromTable(table);
			
			String sql = tableCommandProcessor.TableDataCommandString(table.TableName, viewDef, "");
		
			// cur from old code
			int status = 0;

			int modCount = 0;
			foreach (DataRow dr in table.Rows)
				if (dr.RowState != DataRowState.Unchanged)
					++modCount;
			statusMessage = String.Format("{0} changed rows", modCount);
			try
			{
				// do it.
				SqlDataAdapter myDataAdapter = new SqlDataAdapter();
				// the sql and the command builder automatically generate the 
				// insert/update/delete sql
				SqlConnection databaseConnection = new SqlConnection(ConnectionString);
				databaseConnection.Open();
				
				// here check and rebuild the sql to remove computed columns
				// its a hack for now as I don't like this solution
				sql = RemoveComputed(databaseConnection, table.TableName, sql);

				myDataAdapter.SelectCommand = new SqlCommand(sql, databaseConnection);
				SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);

				// Without the SqlCommandBuilder this line would fail
				// below apparently checks the state of each row in the table
				// and applies the correct sql to update it.
				myDataAdapter.Update(table);
				databaseConnection.Close();
				status = 1;
			}
			catch (DBConcurrencyException e)
			{
				statusMessage = e.Message;
				status = -3;
				//MessageBox.Show(message);
			}
			catch (InvalidOperationException e)
			{
				statusMessage = e.Message;
				status = -2;
			}
			catch (SqlException e)
			{
				statusMessage = e.Message;
				status = -1;
			}
			return (status == -1) ? false : true;
		}




		// This removes computed columns from the insert / update adapter SQL
		// When left in they mess up the insert or update statement.
		private string RemoveComputed(SqlConnection databaseConnection, string tableName, string sql)
		{
			String template = @"
Select name 
  from sys.columns
 where object_id = OBJECT_ID('{0}') 
   and is_computed = 1 
";
			DataTable dt = new DataTable();
			String computedSql = String.Format(template, tableName);
			SqlDataAdapter myDataAdapter = new SqlDataAdapter();
			myDataAdapter.SelectCommand = new SqlCommand(computedSql, databaseConnection);
			SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);
			myDataAdapter.Fill(dt);
			// nothing to do (normal (hopefully)
			if (dt.Rows.Count == 0)
				return sql;
			// do something from here
			foreach (DataRow dr in dt.Rows)
			{
				String computedCol = dr[0].ToString();
				sql = RemoveComputedColumn(tableName, computedCol, sql);
			}
			return sql;
		}

		private string RemoveComputedColumn(string tableName, string computedCol, string sql)
		{
			computedCol = Helper.SafeSqlField(computedCol);
			// This will normally find it
			String seek = computedCol+",";
			if (sql.IndexOf(seek) != -1)
				return sql.Replace(seek, "");
			// It's the last
			seek = ","+computedCol;
			if (sql.IndexOf(seek) != -1)
				return sql.Replace(seek, "");
			// Formatting was changed.
			seek = ", "+computedCol;
			if (sql.IndexOf(seek) != -1)
				return sql.Replace(seek, "");
			return sql;
		}
		
		public String GetSourceCode(DbView.Command command)
		{
			// delegated.
			return sourcecodeCommandProcessor.ProcessCommand(command);
		}

		// wrong. This is search source code
		public String GetSourceCode(DbView.ViewContext context, String objectName)
		{
			return sourcecodeCommandProcessor.GetSourceCode(context, objectName);
		}

		// Delegates 
		public DbView.Command ExecuteDriverAction(DbView.Command command, object window, DbViewDriver.IConnectionConfig connectionConfig, String searchText, out bool refreshGrid)
		{
			DbView.Command nextCommand;
			refreshGrid = extensionCommandProcessor.ProcessCommand(command, window, connectionConfig, searchText, out nextCommand);
			return nextCommand;
		}

		public void GetTableSchema(String tableName, out DataTable table, bool full)
		{
			String template = (full) ? SqlServerTemplates.Templates.FetchTemplate(SqlServerTemplates.MapVersion(this.sqlVersion), DbView.Commands.Cmnd.Fields)
			                         : SqlServerTemplates.FIELDS_SQLTEMPLATE_LITE;
			// this is for LITE version
			String [] tableParts = Helper.ParseTableName(tableName);
			String schemaName = (tableParts.GetLength(0) == 2) ? tableParts[1] : "dbo";
			String command = String.Format(template, tableParts[0], schemaName);
			// this is for full version. Uses OBJECT_ID, which does not 
			// require separating table name and schema.
			if (full) 
				command = GetSchemaSql(tableName);
			
			DataSet dataset = new DataSet();
			Helper.GenericMakeDset(this.connectionString, command, tableName, out dataset);
			table = dataset.Tables[0];
		}

		// Configure driver
		public bool ConfigureConnection(DbViewDriver.IConnectionConfig connectionConfig)
		{
			// display the dialog.
			using (SqlServerConnectionEditForm dlg = new SqlServerConnectionEditForm(connectionConfig))
			{ return (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) ? true : false; }
		}
		
		public String GetSchemaSql(String tableName)
		{
			// get the correct template
			String sqlTemplate = SqlServerTemplates.Templates.FetchTemplate(SqlServerTemplates.MapVersion(this.sqlVersion), DbView.Commands.Cmnd.Fields);
			// remove special where clause as the result should always include all fields.
			sqlTemplate = SqlServerTemplates.Templates.ProcessFilterClause(sqlTemplate, "", isDbCaseSensitive);
			// return template with required tablename added
			return String.Format(sqlTemplate, tableName);
		}
		
		public DbViewDriver.IDatabaseExportSource DatabaseExportSource 
		{
			get{ return new SqlServerImportExportInterfaceImplementation(); }
			set {} // don't bother when 
		}

		public object TryToGetOptionalInterface(DbViewDriver.OptionalInterface requestedInterface)
		{
			if (requestedInterface == DbViewDriver.OptionalInterface.Export)
				return this.DatabaseExportSource;
			if (requestedInterface == DbViewDriver.OptionalInterface.Schema ||
				requestedInterface == DbViewDriver.OptionalInterface.Lookup)
				return new SqlServerOptionInterfaceImplementations(this);
			return null;
		}
		// End of IDataSource interface.
		//////////////////////////////////////////////////////////

		
		// Static methods used 
		// YOu can connect to Express either via a file or via a server
		// this detects the file case.
		public static bool IsExpressFile(DbView.ConnectionInfo connectionInfo)
		{
			return (connectionInfo.Name.ToUpper().IndexOf(".MDF") != -1) ? true : false;
		}
		
		public static bool IsTrustedConnection(DbView.ConnectionInfo connectionInfo)
		{
			return (connectionInfo.UID.Length == 0) ? true : false;
		}

		public static String MakeConnectionStringEx(DbView.ConnectionInfo connectionInfo, bool noApplication, bool forInformation)
		{
			String applicationName = (noApplication) ? "" : "Application Name=DbViewSharp;";
			String password = (connectionInfo.Encrpyted) ? (forInformation) ?  "******" : DbView.ConnectionInfo.DecryptPWD(connectionInfo.PWD)
														 : connectionInfo.PWD;
			
			// Connect to Sql server with a named account
			String connstr = String.Format("data source={0};initial catalog={1};user id={2};password={3};{4}",
								connectionInfo.Server, connectionInfo.Database, 
								connectionInfo.UID, password, applicationName);
			// ... unless its Sql Server express
			if (IsExpressFile(connectionInfo))
				connstr = String.Format("Data Source=.\\SQLEXPRESS;AttachDbFilename={0};Integrated Security=True;Connect Timeout=30;User Instance=True", connectionInfo.Name);
			// or a trusted connection (ie use Windows NT account)
			else if (IsTrustedConnection(connectionInfo))
				connstr = String.Format("data source={0};initial catalog={1};Trusted_Connection=yes;{2}",
								connectionInfo.Server, connectionInfo.Database, applicationName);

			return connstr;
		}
		
		// Original interface: used for connecting properly
		public static String MakeConnectionString(DbView.ConnectionInfo connectionInfo)
		{
			return MakeConnectionStringEx(connectionInfo, false, false);
		}

		public static String MakeConnectionString(DbView.ConnectionInfo connectionInfo, bool noApplication)
		{
			String connStr = MakeConnectionStringEx(connectionInfo, noApplication, false);
			return connStr;
		}

		public static DbView.ConnectionInfo InfoFromConnectionString(string connectionString)
		{
			// see above
			DbView.ConnectionInfo ci = new DbView.ConnectionInfo();
			ci.Database = ExtractConnectionStringParam(connectionString, "initial catalog");
			ci.Server = ExtractConnectionStringParam(connectionString, "data source");
			ci.UID = ExtractConnectionStringParam(connectionString, "user id");
			ci.PWD = ExtractConnectionStringParam(connectionString, "password");
			return ci;
		}

		static String ExtractConnectionStringParam(String connectionString, String param)
		{
			int start = connectionString.ToUpper().IndexOf(param.ToUpper());
			if (start == -1) return "";
			start += param.Length + 1;
			int end = connectionString.IndexOf(";", start);
			if (end == -1) end = connectionString.Length;
			return connectionString.Substring(start, end - start);
		}

		public static bool IsValid(DbView.ConnectionInfo connectionInfo)
		{
			// all commections need a name
			if (connectionInfo.Name.Length == 0) return false;
			// sql express connections just need the name = data file
			if (IsExpressFile(connectionInfo)) return true; // HMM
			// others need a database and server
			if (connectionInfo.Server.Length == 0 || connectionInfo.Database.Length == 0)
				return false;
			// It may be OK without user and pwd as it will switch to 
			// trusted connection
			return true;
		}

		// exposed access for helper (processor classes)
		internal bool IsCaseSensitive {get { return isDbCaseSensitive; } }
		internal int SqlVersion {get { return SqlServerTemplates.MapVersion(this.sqlVersion); } }
		
		//////////////////// helpers ////////
		internal SqlTableSchema MakeSchemaFor(String tableName)
		{
			return Helper.MakeSchemaFor(tableName, connectionString);
			//if (this.m_SqlVersion == version.UNKNOWN) DetermineSqlProperties();
			//String commandString = this.GetSchemaSql(tableName);
			//DataSet DSet = new DataSet();
			//Helper.GenericMakeDset(this.m_Connstr, commandString, tableName, out DSet);
			//DataTable schemaTable = DSet.Tables[0];
			//schemaTable.TableName = tableName;
			//SqlTableSchema schema = new SqlTableSchema();
			//schema.Populate(schemaTable, "Field Name", "Type", "Length", "PKey?", "Auto?",
			//                             "FKey?", "Index?", "Calc?", "Def?", "Default/Computed", "nulls?");

			//return schema;
		}

		String SourceCommand(DbView.ViewContext context)
		{
			String objectName = ObjectNameFromContext(context);
			return objectName; // see GetSource below. Only requires the name
		}

		String ObjectNameFromContext(DbView.ViewContext context)
		{
			// works for views, tables, Procs.
			return context.ObjectName;
		}
		
		// The version may decide the sql template to use.
		SqlServerTemplates.Version DetermineSqlProperties()
		{
			String command = SqlServerTemplates.PROPERTIES_SQL;
			int versionColIndex = 0;
			int caseColIndex = 2;
			DataSet dataset = new DataSet();
			Helper.GenericMakeDset(this.connectionString, command, "ServerProperties", out dataset);
			// Version
			String value = dataset.Tables[0].Rows[0][versionColIndex].ToString();
			sqlVersion = SqlServerTemplates.DetermineSqlVersion(value);
			// Case sensitivity
			value = dataset.Tables[0].Rows[0][caseColIndex].ToString();  // Collation
			// _CI_ specifies case-insensitive, _CS_ (or nothing?) specifies case-sensitive.
			this.isDbCaseSensitive = (value == "Y");
			return sqlVersion;
		}

		/////////////////////////////////////////////////////////////////
		// private
		String connectionString;
		DbView.ConnectionInfo connectionInfo;
		// requires state (curse it).
		ExtensionCommandProcessor extensionCommandProcessor;
		TableCommandProcessor tableCommandProcessor;
		SourcecodeCommandProcessor sourcecodeCommandProcessor;
		
		SqlServerTemplates.Version sqlVersion = SqlServerTemplates.Version.UNKNOWN;   // SQl server version 
		bool isDbCaseSensitive = false;

	}
}
